﻿using System.Data;
using System.IO;

using System;
using System.Windows;
using System.Windows.Controls;
using System.Data.SQLite;

namespace Parking
{
    public class DBBase
    {
        public string dbfile = "parkon.db";

        public DBBase()
        {
            if (!File.Exists(dbfile))
            {
                CreateTables();
                //throw new Exception("Отсутствует файл " + dbfile+ "\nДальнейшая работа программы невозможна");
            }
        }

        private SQLiteConnection _sqlCon;

        private SQLiteCommand _sqlCmd;

        public void SetConnection()
        {
            CreateTables();

            try
            {
                
                _sqlCon = new SQLiteConnection("Data Source=parkon.db;");
            }
            catch (Exception e)
            {
                MessageBox.Show("SetConnection new SQLiteConnection " + e.Message);
            }
        }

        public void CreateTables()
        {
            bool FileExists = File.Exists("parkon.db");
            if (!FileExists)
            {
                //SELECT indate,driver,car,number,note FROM cars where outdate is null
                
                _sqlCon = new SQLiteConnection("Data Source=parkon.db; Version=3;New=True;");
                _sqlCon.Open();
                _sqlCmd = _sqlCon.CreateCommand();
                _sqlCmd.CommandText = @"
BEGIN TRANSACTION;
CREATE TABLE cars (
    [Id] INTEGER PRIMARY KEY NOT NULL, 
    [indate] TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    [outdate] TEXT, 
    [driver] TEXT, 
    [car] TEXT NOT NULL, 
    [number] TEXT, 
    [region] TEXT, 
    [note] TEXT,
    [sent] TEXT
    );
CREATE TABLE operator (
    name TEXT,
    surname TEXT,
    otch TEXT,
    login TEXT,
    passw TEXT,
    lastlogin TEXT
);
COMMIT;
";
                try
                {
                    _sqlCmd.ExecuteNonQuery();
                }
                catch (SQLiteException e)
                {
                    MessageBox.Show(e.Message);
                }
                _sqlCon.Close();

            }
        }

        private void ExecuteQuery(string txtQuery)
        {
            SetConnection();
            _sqlCon.Open();
            _sqlCmd = _sqlCon.CreateCommand();
            _sqlCmd.CommandText = txtQuery;
            int result = _sqlCmd.ExecuteNonQuery();
            _sqlCon.Close();
        }

        public DataTable LoadAllData()
        {
            SetConnection();

            try
            {
                _sqlCon.Open();
            }
            catch (SQLiteException e)
            {
                MessageBox.Show(e.Message);
                return null;
            }

            _sqlCmd = _sqlCon.CreateCommand();
            const string commandText = "SELECT id,indate,driver,car,number,region,note,outdate,sent FROM cars";
            var db = new SQLiteDataAdapter(commandText, _sqlCon);
            var ds = new DataSet();
            ds.Reset();
            db.Fill(ds);
            var res = ds.Tables[0];
            _sqlCon.Close();
            return res;
        }

        public DataTable LoadData()
        {
            try
            {
                SetConnection();
            }
            catch(Exception ex)
            {
                MessageBox.Show("SetConnection error " + ex.Message);
                return null;
            }

            try
            {
                _sqlCon.Open();
            }
            catch (SQLiteException e)
            {
                MessageBox.Show(e.Message);
                return null;
            }

            _sqlCmd = _sqlCon.CreateCommand();
            const string commandText = "SELECT id as №,indate as Въезд,driver as Водитель,car as Машина,number as Номер,region as Регион,note as Примечание FROM cars WHERE outdate is null";
            var db = new SQLiteDataAdapter(commandText,_sqlCon);
            var ds = new DataSet();
            ds.Reset();
            int FillResult = db.Fill(ds);
            var res = ds.Tables[0];
            _sqlCon.Close();
            return res;
        }

        public void WriteData(string number, int region, string driver, string note, string time, int type, string car)
        {
            if(type == 1)
            {
                SetConnection();
                _sqlCon.Open();
                _sqlCmd = _sqlCon.CreateCommand();
                var commandText = "SELECT [id] FROM cars WHERE outdate is null AND [region] = '" + region + "' AND [driver] = '" + driver + "' AND [number] = '" + number + "' AND [car] = '" + car + "' ";

                var db = new SQLiteDataAdapter(commandText,_sqlCon);
                var ds = new DataSet();
                var dt = new DataTable();
                ds.Reset();

                db.Fill(ds);

                dt = ds.Tables[0];
                _sqlCon.Close();
                if(dt.Rows.Count>0)
                {
                    commandText = "UPDATE cars SET [outdate] = '" + time + "' AND [note] = '" + note + "' WHERE  outdate is null AND [region] = '" + region + "' AND [driver] = '" + driver + "' AND [number] = '" + number + "' AND [car] = '" + car + "' ";
                    ExecuteQuery(commandText);
                }
                else
                {
                    commandText = "INSERT INTO cars(" + (type == 0 ? "[indate]" : "[outdate]") + ", [driver], [car],  [number], [region], [note]) VALUES ('" + time + "', '" + driver + "', '" + car +
                                      "', '" + number + "', '" + region + "', '" + note + "');";
                    ExecuteQuery(commandText);
                }
            }
            else
            {
                var commandText = "INSERT INTO cars(" + (type == 0 ? "[indate]" : "[outdate]") + ", [driver], [car],  [number], [region], [note]) VALUES ('" + time + "', '" + driver + "', '" + car +
                                  "', '" + number + "', '" + region + "', '" + note + "');";
                ExecuteQuery(commandText);
            }
        }

        public void SetDeparture(string id, string time)
        {
            string commandText = "UPDATE cars SET [outdate] = '" + time + "' where id='" + id + "' ";
            ExecuteQuery(commandText);
        }

        public void SetSent(string id, string time)
        {
            string commandText = "UPDATE cars SET [sent] = '" + time + "' where id='" + id + "' ";
            ExecuteQuery(commandText);
        }
    }
}
